package org.eclipse.epsilon.emc.rdbms.core.sqlstatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import org.eclipse.epsilon.emc.rdbms.core.customexceptions.CustomException;
import org.eclipse.epsilon.emc.rdbms.core.imodel.DBReflexiveModelElement;
import org.eclipse.epsilon.emc.rdbms.core.persistancebroker.PersistanceBroker;
import org.eclipse.epsilon.emc.rdbms.core.persistancemechanism.RelationalDatabase;



public  class UpdateSQLStatement extends SQLStatement
{
	/** This method is used to generate the update statement.
	 * @param tableInfo This is a result set containing the database,schema and tablename
	 * @param properties This is the list of attributes and values to be updated
	 * @criteria This is the list of attributes and values to specify in the where clause of the statement
	 * @return statement The statement that can be executed on the database server.
	*/	
	@Override
	public String BuildForCriteria(ResultSet tableInfo, HashMap properties,HashMap<String, Object> criteria) throws SQLException 
	{
		RelationalDatabase connection = PersistanceBroker.connectTo();
		String  statement = "";
		
		//Here we need to remove some properties as we dont want to update any columns involved with the Primark Key.
		while (tableInfo.next())
		{
			databaseName = tableInfo.getString("TABLE_CAT");
			schemaName = tableInfo.getString("TABLE_SCHEMA");
			tableName = tableInfo.getString("TABLE_NAME");	
		}
		
		
        ResultSet primaryKeyName = connection.getPrimaryKey(tableName);
		while(primaryKeyName.next())
		{
		  if(properties.containsKey(primaryKeyName.getString("COLUMN_NAME")))
		  {
			new CustomException("You cannot update the primary Key Value");  
          properties.remove(primaryKeyName.getString("COLUMN_NAME"));
		  }
		}
		
		if(properties !=null && properties.size()>0) //We need to have some properties to update else we dont build the update statement.
		{
				statement = connection.getClauseStringForUpdate()+databaseName+"."+schemaName+"."+tableName;
				//Now we know there is something in the hashmap we need to see if this is a column value or a foreign key relationship specified.
				ResultSet tableColumns = connection.getSQLColumns(tableName);
				boolean isColumn = false;
				String FKdatabaseName = null;
				String FKschemaName = null;
				String FKtableName=null;
				
				while(tableColumns.next())
				{
					//We need to check if this is a column in the table
					if(tableColumns.getString(4).toUpperCase().equals(getKeyFromHashMap(properties).toUpperCase()))
					{
					//We know that this is a column in the table
						isColumn=true;
						statement = statement + " SET "+ getStringFromHashMap(properties,",");
					}
				}
				if(isColumn==false)
				{
				//We know that this is not a column in a table and therefore we need to check if it is a foreign Key relationship specified.	
				ResultSet ForeignKeys = connection.getImportedKeys(tableName);
					
				while(ForeignKeys.next())
				{
					
					if(ForeignKeys.getString(12).toUpperCase().equals(getKeyFromHashMap(properties).toUpperCase()))
				    {
						
					//We know that this is a foreign Key relationship definition in the table
	                //So what we need to do is to nest a select statement in here.
					
					ResultSet FKTableInfo = connection.getSQLTables(ForeignKeys.getString("FKTABLE_NAME"));
					while (FKTableInfo.next())
					{
						FKdatabaseName = FKTableInfo.getString("TABLE_CAT");
						FKschemaName = FKTableInfo.getString("TABLE_SCHEMA");
						FKtableName = FKTableInfo.getString("TABLE_NAME");	
					}
					//statement  = statement + " SET " + ForeignKeys.getString("FKCOLUMN_NAME")+ " = (SELECT "+ForeignKeys.getString("PKCOLUMN_NAME")+" FROM "+FKdatabaseName+"."+FKschemaName+"."+FKtableName+ " WHERE " +getStringFromHashMap(((DBReflexiveModelElement) properties.get("FK_Customer_Address")).getProps(),"AND")+")";
					statement  = statement + " SET " + ForeignKeys.getString("FKCOLUMN_NAME")+ " = (SELECT "+ForeignKeys.getString("PKCOLUMN_NAME")+" FROM "+FKdatabaseName+"."+FKschemaName+"."+FKtableName + 	" WHERE " +getStringFromHashMap(((DBReflexiveModelElement) properties.get(getKeyFromHashMap(properties))).getProps(),"AND")+")";
				    }
				}
				}			
		if(criteria !=null)
		{
			if(criteria.size()>0)
			{
				statement = statement + " WHERE "+getStringFromHashMap(criteria,"AND");
			}
		}
		}
		return statement;
	}

	/** This is not implemented
	*/	
    @Override
	public String BuildForCriteria(ResultSet sqlTables,HashMap<String, Object> props) throws SQLException 
	{
		return null;
	}

    /** This is not implemented
	*/	
    @Override
	public String BuildForCriteria(ResultSet tableInfo,ArrayList<String> properties, HashMap<String, Object> criteria)throws SQLException 
	{
		return null;
	}

    /** This is not implemented
	*/	
	@Override
	public String BuildForCriteria(ResultSet tableInfo, ResultSet sqlColumns,
			ResultSet primaryKey, ResultSet columnMetaData,
			HashMap<String, Object> properties) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	/** This is not implemented
	*/	
	@Override
	public String BuildForCriteria(ResultSet sqlTables,
			ArrayList<String> columns, ArrayList PKtables, ArrayList FKtables,
			ArrayList PKcols, ArrayList FKcols,
			HashMap<String, Object> parameters) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
}
